/**
 * 
 */
package org.jeecgframework.poi.util;

import java.text.DecimalFormat;
import java.text.NumberFormat;

import org.apache.poi.ss.formula.functions.Value;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @author xfworld
 * @since 2015-12-28
 * @version 1.0
 * @see org.jeecgframework.poi.util.PoiCellUtil
 * 获取单元格的值
 */
public class PoiCellUtil {
    /**
     * 读取单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getCellValue(Sheet sheet, int row, int column) {
        String value = null;
        if (isMergedRegion(sheet, row, column)) {
            value = getMergedRegionValue(sheet, row, column);
        } else {
            Row rowData = sheet.getRow(row);
            Cell cell = rowData.getCell(column);
            value = getCellValue(cell);
        }
        return value;
    }

    /**  
     * 获取合并单元格的值  
     * @param sheet  
     * @param row  
     * @param column  
     * @return  
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);

                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }

    /**  
     * 判断指定的单元格是否是合并单元格  
     * @param sheet  
     * @param row  
     * @param column  
     * @return  
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {

                    return true;
                }
            }
        }

        return false;
    }

    
    /**
     * 先拿合并的列数
     * 如果是双向合并,拿到的是行合并的大小
     * @author 张栋  2016年8月2日下午2:10:55
     */
    public static int mergedSize(Sheet sheet,int row,int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                	if(firstColumn == lastColumn){
                		return lastRow - firstRow;
                	}else{
                		return lastColumn - firstColumn;
                	}
//                	if(firstRow ==lastRow){
//                		return lastColumn - firstColumn;
//                	}else{
//                		return lastRow - firstRow;
//                	}
                }
            }
        }

        return 0;//none
    }
    
    /**
     * @param sheet
     * @param row   
     * @param column
     * @return  1 是合并的行   2是合并的列 cell =cell  ,3双向合并
     * @author 张栋  2016年5月20日上午10:21:38
     */
    public static int  mergedFrom(Sheet sheet,int row,int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                	if(firstRow ==lastRow){
                		return 1;//row
                	}else if(firstColumn == lastColumn){
                		return 2;//column
                	}else{
                		return 3;//
                	}
                }
            }
        }

        return 0;//none
    }
    
    
    /**  
     * 获取单元格的值  
     * @param cell  
     * @return  
     */
    public static String getCellValue(Cell cell) {

        if (cell == null)
            return "";

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

            return cell.getStringCellValue();

        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        	String value =null;
        	try {
				value =  String.valueOf(cell.getNumericCellValue());
			} catch (Exception e) {
				try{
					value =  String.valueOf(cell.getRichStringCellValue());
				}catch(Exception e2){
					value =cell.getCellFormula(); 
				}
			}
            return value;

        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        	return  decimalFormat.format(cell.getNumericCellValue());
        }

        return "";
    }

	public static String getCellValue(Cell cell, CellValue formulaValue) {
		
		if(formulaValue!=null){
			return getFormlaValue(formulaValue);
		}else{
			return getCellValue(cell);
		}
	}
	
	private static DecimalFormat decimalFormat = new DecimalFormat("0.####");
	public static String getFormlaValue(CellValue formulaValue){
		String result ="";
		switch (formulaValue.getCellType()) {  
	    case Cell.CELL_TYPE_BOOLEAN:  
	    	 result =  String.valueOf(formulaValue.getBooleanValue());
	        break;  
	    case Cell.CELL_TYPE_NUMERIC:  
	    	result = decimalFormat.format(formulaValue.getNumberValue());
	        break;  
	    case Cell.CELL_TYPE_STRING:  
	    	result =  String.valueOf(formulaValue.getStringValue());  
	        break;  
	    case Cell.CELL_TYPE_BLANK:  
	        break;  
	    case Cell.CELL_TYPE_ERROR:  
	        break;  
	  
	    // CELL_TYPE_FORMULA will never happen  
	    case Cell.CELL_TYPE_FORMULA:   
	        break;  
		}
		return result;
	}
}
